package com.brother.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.brother.database.DataBase;
import com.brother.ibatis.ClientFactory;
import com.brother.model.Report;
import com.brother.model.Tracker;
import com.brother.util.CommonProperties;
import com.brother.util.MyPaginatedList;
import com.brother.util.ReportTableUtils;
import com.ibatis.common.util.PaginatedList;
import com.ibatis.sqlmap.client.SqlMapClient;

public class ReportService extends BaseService{
	private TrackerService tkSrv = new TrackerService();
	private Logger log = Logger.getLogger(ReportService.class);
	
	public PaginatedList findData(String pageNo,int pageLimit,Report search){
		
//		SqlMapClient map = ClientFactory.getSqlMapper();
		StringBuffer sql = new StringBuffer();
		Tracker tracker = tkSrv.getTrackerByFieldName("trackerid", search.getTrackerid());
		try {
			search = search.clone();
			//search.setCreateTimeTo(search.getCreateTimeTo()+" 23:59:59");
			if("allalarm".equalsIgnoreCase(search.getState()))
				search.setAlarmTypeName("allalarm");
			sql.append("from r_" + tracker.getUserTrackerId());
			sql.append(" r ");
			String prefix = " where ";
			if(search.getReportId() != 0){
				sql.append(prefix +" r.reportId="+search.getReportId());
				prefix = " and ";
			}
			
			if(search.getCreateTimeFrom() != null){
				sql.append(prefix +" r.createTime>='"+search.getCreateTimeFrom()+"' ");
				prefix = " and ";
			}
			if(search.getState() != null && !search.getState().equals("")){
				if(search.getState().equals("1")){
					sql.append(prefix +" (r.state !='' and r.state is not null) ");
				}else{
					sql.append(prefix +" r.state ='"+search.getState()+"'  ");
				}
				prefix = " and ";
			}
			if(search.getCreateTimeTo() != null){
				sql.append(prefix +" r.createTime<'"+search.getCreateTimeTo()+"' ");
				prefix = " and ";
			}
			
		} catch (CloneNotSupportedException e1) {
			e1.printStackTrace();
		}
		
		try {
			log.debug("sql="+sql.toString());
			int fromSize = Integer.parseInt(pageNo) * pageLimit;
			int totalSize = 0;
			ResultSet rs = DataBase.query("select count(r.reportid) as num "+sql.toString()+" ");
			rs.next();
			totalSize = rs.getInt("num");
			int totalPage = totalSize % pageLimit == 0 ? (totalSize / pageLimit) : (totalSize / pageLimit + 1);
			MyPaginatedList mp = new MyPaginatedList(totalSize, totalPage, pageLimit, Integer.parseInt(pageNo));
			rs = DataBase.query("select SQL_BUFFER_RESULT  r.* "
					+sql.toString()+" order by createTime asc limit "+fromSize+","+pageLimit);
			//paginated = map.queryForPaginatedList("selectReportByExample", search, pageLimit);
			//paginated.gotoPage(Integer.parseInt(pageNo));
			setReportObj(rs, mp, tracker);
			mp.gotoPage(Integer.parseInt(pageNo));
			return mp;
		} catch (SQLException e) {
			Logger.getLogger(this.getClass()).error(e);
		} finally {
			DataBase.closeThreadConnection();
		}
		return new MyPaginatedList(0,0,pageLimit, Integer.parseInt(pageNo));
	}	
	
	
	private void setReportObj(ResultSet rs, MyPaginatedList mp, Tracker tracker) throws SQLException {
		
		while(rs.next()){
			Report r = new Report();
			setModele(r, rs);
			r.setTracker(tracker);
			mp.add(r);
		}
	}

	private void setModele(Report r, ResultSet rs) throws SQLException {
		r.setReportId(rs.getInt("reportId"));
		r.setTrackerid(rs.getString("trackerid"));
		r.setCreateTime(rs.getTimestamp("createTime"));
		r.setAddress(rs.getString("address"));
		r.setLat(rs.getFloat("lat"));
		r.setLng(rs.getFloat("lng"));
		r.setSignedLat(rs.getFloat("signed_lat"));
		r.setSignedLng(rs.getFloat("signed_lng"));
		r.setSpeed(rs.getFloat("speed"));
		r.setOrient(rs.getString("orient"));
		r.setState(rs.getString("state"));
		r.setSignal(rs.getString("signal"));
		r.setLanguage(rs.getString("language"));
		r.setOrig_lat(rs.getString("orig_lat"));
		r.setOrig_lng(rs.getString("orig_lng"));
		r.setLatOrient(rs.getString("latOrient"));
		r.setLngOrient(rs.getString("lngOrient"));
		
//		r.setVoltage(rs.getString("voltage"));
//		r.setBattery(rs.getString("battery"));
//		r.setFuel(rs.getString("fuel"));
//		r.setAd2(rs.getString("ad2"));
//		r.setTemp(rs.getString("temp"));
//		r.setFlag(rs.getString("flag"));
//		r.setComment(rs.getString("command"));
		
	}

	public boolean hasTheSameData(String trackerId, Date date, float lat, float lng){
		SimpleDateFormat df = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
		String sql = "select * from report where trackerid='"+trackerId
			+"' and date='"+df.format(date)+"' and (signedLat-"+lat
			+" < 0.0003) and (signedLat-"+lat+">-0.0003) and (signedLng-"+lng+"<0.0003) and " +
					" (signedLng-"+lng+">-0.0003) ";
		try{
			if(DataBase.query(sql).next()){
				return true;
			}
		}catch(Exception e){
			DataBase.closeConnection();
		}
		return false;
	}
	

//	public int findDataSize(int pageLimit,Report search){
//		int size = 0;
//		try {
//			search = search.clone();
//			search.setCreateTimeTo(search.getCreateTimeTo()+" 23:59:59");
//			if("allalarm".equalsIgnoreCase(search.getState()))
//				search.setAlarmTypeName("allalarm");
//		} catch (CloneNotSupportedException e1) {
//			e1.printStackTrace();
//		}
//		SqlMapClient map = ClientFactory.getSqlMapper();
//		
//		try {
//			size = (Integer) map.queryForObject("selectCountReportByExample", search, pageLimit);
//		} catch (SQLException e) {
//			Logger.getLogger(this.getClass()).error(e);
//		}
//		return size;	
//	}
	
//	public void insertData(Report data){
//		SqlMapClient map = ClientFactory.getSqlMapper();
//		try {
//			map.insert("insertReport", data);
//		} catch (SQLException e) {
//			Logger.getLogger(this.getClass()).error(e);
//		}
//	}
	
	public void updateReport(Report r){
		SqlMapClient map = ClientFactory.getSqlMapper();
		try {
			map.insert("updateReport", r);
		} catch (SQLException e) {
			Logger.getLogger(this.getClass()).error(e);
		}		
	}

	/**检查是否具有相应的数据表，每个跟踪器一个数据表
	 * @param imei
	 * @return
	 */
	private boolean hasTable(String imei){
		try{
			String sql = "select * from r_"+imei+" limit 0,1";
			DataBase.query(sql).next();
			return true;
		}catch(Exception e){
			if(e.getMessage().indexOf("doesn't exist") > -1){
				return false;
			}
		}finally{
			DataBase.closeConnection();
		}
		return false;
	}
	
	public void insertData(Report data, Tracker tracker) {
		// 没有该跟踪器的表格则创建一个表
		if(!ReportTableUtils.getInstance().hasTable(tracker.getUserTrackerId())){
			boolean has = hasTable(tracker.getUserTrackerId());
			if(has){
				ReportTableUtils.getInstance().setImei(tracker.getUserTrackerId(), has);
			}else{
				has = this.createTable(tracker.getUserTrackerId());
				if(has){
					ReportTableUtils.getInstance().setImei(tracker.getUserTrackerId(), has);
				}else{
					Logger.getLogger(this.getClass()).error("Caution.......\r\nCan't create a report table r_"+tracker.getUserTrackerId());
					return;
				}
			}
		}
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		// 插入数据表
		StringBuffer sb = new StringBuffer("insert into r_"+tracker.getUserTrackerId()+"(");
		sb.append("trackerId,");
		sb.append("address,");
		sb.append("createTime,");
		sb.append("orig_lat,");
		sb.append("lat,");
		sb.append("lng,");
		sb.append("latOrient,");
		sb.append("lngOrient,");
		sb.append("orig_lng,");
		sb.append("speed,");
		sb.append("orient,");
		sb.append("state,");
		sb.append("`signal`,");
		sb.append("language,");
		sb.append("signed_lat,");
		sb.append("signed_lng,");
		
		sb.append("voltage,");
		sb.append("battery,");
		sb.append("fuel,");
		sb.append("ad2,");
		sb.append("temp,");
		sb.append("flag,");
		sb.append("command");
		sb.append(") ");
		
		sb.append("values(");
		sb.append("'"+ data.getTrackerid() +"',");
		if(data.getAddress() != null){
			String address = data.getAddress().replaceAll("\\\\", "\\\\\\\\");
			address = address.replaceAll("'", "\\\\'");
			sb.append("'"+ address +"',");
		}else
			sb.append("null,");
		sb.append("'"+ format.format(data.getCreateTime()) +"',");
		sb.append("'"+ data.getOrig_lat() +"',");
		sb.append(""+ data.getLat() +",");
		sb.append(""+ data.getLng() +",");
		sb.append("'"+ data.getLatOrient() +"',");
		sb.append("'"+ data.getLngOrient() +"',");
		sb.append("'"+ data.getOrig_lng() +"',");
		sb.append(""+ data.getSpeed() +",");
		sb.append("'"+ data.getOrient() +"',");
		sb.append("'"+ data.getState() +"',");
		sb.append("'"+ data.getSignal() +"',");
		sb.append("'"+ data.getLanguage() +"',");
		sb.append(data.getSignedLat() + ",");
		sb.append(data.getSignedLng()+",");
		
		sb.append("'"+ data.getVoltage() +"',");
		sb.append("'"+ data.getBattery() +"',");
		sb.append("'"+ data.getFuel() +"',");
		sb.append("'"+ data.getAd2() +"',");
		sb.append("'"+ data.getTemp() +"',");
		sb.append("'"+ data.getFlag() +"',");
		sb.append("'"+ data.getFlag() +"'");
		
		
		sb.append(") ");
		
		try{
			DataBase.update(sb.toString());
		}catch(Exception e){
			if(e.getMessage().indexOf("doesn't exist") > -1){
				try{
					this.createTable(tracker.getUserTrackerId());
					DataBase.update(sb.toString());
				}catch(Exception e2){
					log.error(sb.toString(), e2);
				}
			}else
				log.error(sb.toString(), e);
		}finally{
			DataBase.closeConnection();
		}
		
	}


	/**根据imei号创建一个历史数据的表格
	 * @param userTrackerId
	 */
	private boolean createTable(String imei) {
		try{
			/*CREATE TABLE `report` (
			  `reportId` int(11) NOT NULL AUTO_INCREMENT,
			  `trackerId` varchar(50) DEFAULT NULL,
			  `address` varchar(130) DEFAULT NULL,
			  `createTime` datetime DEFAULT NULL,
			  `orig_lat` varchar(20) DEFAULT NULL,
			  `lat` float(8,6) DEFAULT '0.000000',
			  `lng` float(9,6) DEFAULT '0.000000',
			  `latOrient` varchar(5) DEFAULT 'N',
			  `lngOrient` varchar(5) DEFAULT 'W',
			  `orig_lng` varchar(20) DEFAULT NULL,
			  `speed` float(10,5) DEFAULT '0.00000',
			  `orient` varchar(10) DEFAULT '0',
			  `state` varchar(20) DEFAULT NULL,
			  `signal` varchar(10) DEFAULT 'ok',
			  `language` varchar(10) DEFAULT 'zh_CN',
			  `signed_lat` float(8,6) DEFAULT NULL,
			  `signed_lng` float(9,6) DEFAULT NULL,
			  PRIMARY KEY (`reportId`),
			  KEY `createTime` (`trackerId`),
			) ENGINE=MyISAM DEFAULT CHARSET=utf8*/
			String sql = CommonProperties.getReportTableSchema().replaceFirst("\\{imei\\}", imei);
			Logger.getLogger(this.getClass()).debug("creating table "+sql);
			if(DataBase.update(sql) != -1){
				return true;
			}
		}catch(Exception e){
			Logger.getLogger(this.getClass()).error(e);
		}finally{
			DataBase.closeConnection();
		}
		return false;
	}
	


	public Report getTopData(String imei) {
		try{
			ResultSet rs = DataBase.query("select * from r_"+imei+" order by createTime desc limit 0,1");
			if(rs.next()){
				Report r = new Report();
				setModele(r, rs);
				return r;
			}
		}catch (Exception e) {
			log.error("" + e);
		}finally{
			DataBase.closeConnection();
		}
		return null;
	}

	public List<Report> findAllOneDate(String date, String trackerid){
		List<Report> result = new ArrayList<Report>();
		try{
			Tracker tracker = tkSrv.getTrackerByFieldName("trackerid", trackerid);
			ResultSet rs = DataBase.query("select * from r_"+tracker.getUserTrackerId()
					+" where createtime>='"+date+"' and createtime <='"+date+" 23:59:59'  order by createTime asc");
			
			while(rs.next()){
				Report r = new Report();
				setModele(r, rs);
				result.add(r);
			}
		}catch (Exception e) {
			log.error("" + e);
		}finally{
			DataBase.closeConnection();
		}
		return result;
	}
	
	public List<Report> findAllByRange(String from, String to, String trackerid){
		List<Report> result = new ArrayList<Report>();
		try{
			Tracker tracker = tkSrv.getTrackerByFieldName("trackerid", trackerid);
			ResultSet rs = DataBase.query("select * from r_"+tracker.getUserTrackerId()
					+" where createtime>='"+from+"' and createtime <='"+to+"'  order by createTime asc");
			
			while(rs.next()){
				Report r = new Report();
				setModele(r, rs);
				result.add(r);
			}
		}catch (Exception e) {
			log.error("" + e);
		}finally{
			DataBase.closeConnection();
		}
		return result;
	}
	
}
